package com.demo2do.fms.admin.entity.report.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 *
 * @author Ark
 *
 */
public class OutShipmentReportSearch implements QueryBuilder {

    /** 所选择公司 */
    private List<Long> organizationIds;
    
    /** 安吉调度指令号 */
    private String shipmentNumber;
    
    /** 驳运车号 */
    private Long transMeansId;
    
    /** 起始地 */
    private Long srcWhId;
    
    /** 目的地 */
    private Long destWhId;
    
    /** 承运方*/
    private Long transCorpId;
    
    /** 开始日期 */
    private String beginShipmentDate;
    
    /** 结束日期 */
    private String endShipmentDate;
    
    /** 开始装运日期 */
    private String beginLoadingTime;
    
    /** 结束装运日期 */
    private String endLoadingTime;
    
    /** 开始起运日期 */
    private String beginLeavingTime;
    
    /** 结束起运日期 */
    private String endLeavingTime;
    
    /** 调度指令状态 */
    private Long status;
    
    /** 导出条数限制 */
    private Integer limit;
        
    private QueryOrder order;
    
    private QueryFilter filter;
    
    /**
     * the default constructor
     */
    public OutShipmentReportSearch() {
        order = new QueryOrder();
        order.add("s", "leaving_time", "DESC");
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if(filter == null || refresh) {
            
            filter = new QueryFilter().addEquals("s", "shipment_number", shipmentNumber)
                                      .addEquals("v", "id", transMeansId)
                                      .addEquals("s", "src_wh_id", srcWhId)
                                      .addEquals("s", "dest_wh_id", destWhId)
                                      .addEquals("s", "trans_corp_id", transCorpId);
            
            if (StringUtils.isNotBlank(beginShipmentDate)) {
                filter.addSentence("s.create_time >= TO_DATE('" + beginShipmentDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endShipmentDate)) {
                filter.addSentence("s.create_time <= TO_DATE('" + endShipmentDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(beginLoadingTime)) {
                filter.addSentence("s.loading_time >= TO_DATE('" + beginLoadingTime.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endLoadingTime)) {
                filter.addSentence("s.loading_time <= TO_DATE('" + endLoadingTime.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(beginLeavingTime)) {
                filter.addSentence("s.leaving_time >= TO_DATE('" + beginLeavingTime.replace("-", "") + "000000', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endLeavingTime)) {
                filter.addSentence("s.leaving_time <= TO_DATE('" + endLeavingTime.replace("-", "") + "235959', 'yyyymmddhh24miss')", Collections.emptyMap());
            }
            
            filter.addEquals("s", "status", status)
                  .addSentence("s.balance_id IN (:organizationIds)", "organizationIds", organizationIds)
                  .addLessThanEqual("ROWNUM", limit);
            
        }
        
        return filter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT tc.organization_name AS balanceName, ")
                     .append("s.shipment_number AS shipmentNumber, ")
                     .append("s.shipment_group AS shipmentGroup, ")
                     .append("v.organization_name AS organizationName, ")
                     .append("s.create_time AS createTime, ")
                     .append("w1.facility_code || ' - ' || w1.facility_name AS srcFacility, ")
                     .append("w2.facility_code || ' - ' || w2.facility_name AS destFacility, ")
                     .append("v.vehicle_code || ' - ' || v.vehicle_name AS vehicle, ")
                     .append("S.DRIVE_WAY AS driveWay, ")
                     .append("s.load_quantity AS loadQuantity, ")
                     .append("s.car_summary AS carSummary, ")
                     .append("s.loading_time AS loadingTime, ")
                     .append("s.leaving_time AS leavingTime, ")
                     .append("FC_GET_SHIPMENT_STATUS(s.status) AS shipmentStatus, ")
                     .append("FC_GET_SHIPMENT_METHOD(s.trans_type) AS transType ")
              .append("FROM vw_shipment_line s, vw_client c, vw_warehouse w1, vw_warehouse w2, vw_vehicle v, vw_transportation_company tc ")
              .append("WHERE s.customer_id = c.id(+) AND s.src_wh_id = w1.id AND s.dest_wh_id = w2.id AND s.trans_means_id = v.id AND s.is_disabled = 0 AND tc.id = s.balance_id AND s.trans_corp_id IN (SELECT id FROM vw_transportation_company WHERE is_system = 0)")
              .append(getPreparedFilter(false).clauseSentence() + order.sentence());
        
        return buffer.toString();
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("balanceName", rs.getObject("balanceName"));
                result.put("shipmentNumber", rs.getObject("shipmentNumber"));
                result.put("shipmentGroup", rs.getObject("shipmentGroup"));
                result.put("organizationName", rs.getObject("organizationName"));
                result.put("createTime", rs.getObject("createTime"));
                result.put("srcFacility", rs.getObject("srcFacility"));
                result.put("destFacility", rs.getObject("destFacility"));
                result.put("vehicle", rs.getObject("vehicle"));
                result.put("driveWay", rs.getObject("driveWay"));
                result.put("loadQuantity", rs.getObject("loadQuantity"));
                result.put("carSummary", rs.getObject("carSummary"));
                result.put("loadingTime", rs.getObject("loadingTime"));
                result.put("leavingTime", rs.getObject("leavingTime"));
                result.put("shipmentStatus", rs.getObject("shipmentStatus"));
                result.put("transType", rs.getObject("transType"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return order;
    }
    
    /**
     * @return
     */
    public QueryFilter getFilter(){
        return getPreparedFilter(false);
    }

    /**
     * @return the organizationIds
     */
    public List<Long> getOrganizationIds() {
        return organizationIds;
    }

    /**
     * @return the shipmentNumber
     */
    public String getShipmentNumber() {
        return shipmentNumber;
    }

    /**
     * @return the transMeansId
     */
    public Long getTransMeansId() {
        return transMeansId;
    }

    /**
     * @return the srcWhId
     */
    public Long getSrcWhId() {
        return srcWhId;
    }

    /**
     * @return the destWhId
     */
    public Long getDestWhId() {
        return destWhId;
    }

    /**
     * @return the transCorpId
     */
    public Long getTransCorpId() {
        return transCorpId;
    }

    /**
     * @return the beginShipmentDate
     */
    public String getBeginShipmentDate() {
        return beginShipmentDate;
    }

    /**
     * @return the endShipmentDate
     */
    public String getEndShipmentDate() {
        return endShipmentDate;
    }

    /**
     * @return the beginLoadingTime
     */
    public String getBeginLoadingTime() {
        return beginLoadingTime;
    }

    /**
     * @return the endLoadingTime
     */
    public String getEndLoadingTime() {
        return endLoadingTime;
    }

    /**
     * @return the beginLeavingTime
     */
    public String getBeginLeavingTime() {
        return beginLeavingTime;
    }

    /**
     * @return the endLeavingTime
     */
    public String getEndLeavingTime() {
        return endLeavingTime;
    }

    /**
     * @return the status
     */
    public Long getStatus() {
        return status;
    }

    /**
     * @return the limit
     */
    public Integer getLimit() {
        return limit;
    }

    /**
     * @param organizationIds the organizationIds to set
     */
    public void setOrganizationIds(List<Long> organizationIds) {
        this.organizationIds = organizationIds;
    }

    /**
     * @param shipmentNumber the shipmentNumber to set
     */
    public void setShipmentNumber(String shipmentNumber) {
        this.shipmentNumber = shipmentNumber;
    }

    /**
     * @param transMeansId the transMeansId to set
     */
    public void setTransMeansId(Long transMeansId) {
        this.transMeansId = transMeansId;
    }

    /**
     * @param srcWhId the srcWhId to set
     */
    public void setSrcWhId(Long srcWhId) {
        this.srcWhId = srcWhId;
    }

    /**
     * @param destWhId the destWhId to set
     */
    public void setDestWhId(Long destWhId) {
        this.destWhId = destWhId;
    }

    /**
     * @param transCorpId the transCorpId to set
     */
    public void setTransCorpId(Long transCorpId) {
        this.transCorpId = transCorpId;
    }

    /**
     * @param beginShipmentDate the beginShipmentDate to set
     */
    public void setBeginShipmentDate(String beginShipmentDate) {
        this.beginShipmentDate = beginShipmentDate;
    }

    /**
     * @param endShipmentDate the endShipmentDate to set
     */
    public void setEndShipmentDate(String endShipmentDate) {
        this.endShipmentDate = endShipmentDate;
    }

    /**
     * @param beginLoadingTime the beginLoadingTime to set
     */
    public void setBeginLoadingTime(String beginLoadingTime) {
        this.beginLoadingTime = beginLoadingTime;
    }

    /**
     * @param endLoadingTime the endLoadingTime to set
     */
    public void setEndLoadingTime(String endLoadingTime) {
        this.endLoadingTime = endLoadingTime;
    }

    /**
     * @param beginLeavingTime the beginLeavingTime to set
     */
    public void setBeginLeavingTime(String beginLeavingTime) {
        this.beginLeavingTime = beginLeavingTime;
    }

    /**
     * @param endLeavingTime the endLeavingTime to set
     */
    public void setEndLeavingTime(String endLeavingTime) {
        this.endLeavingTime = endLeavingTime;
    }

    /**
     * @param status the status to set
     */
    public void setStatus(Long status) {
        this.status = status;
    }

    /**
     * @param limit the limit to set
     */
    public void setLimit(Integer limit) {
        this.limit = limit;
    }
    
}
